package org.sumkid.service.impl;


import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.NonUniqueResultException;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.JPAExpressions;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.sumkid.dto.UserDTO;
import org.sumkid.entity.QUser;
import org.sumkid.entity.QUser2018;
import org.sumkid.entity.User;
import org.sumkid.service.UserService;

import java.util.List;

/**
 * Default Description
 *
 * @author sumkid
 * @since 2022/10/2
 */
@Slf4j
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private JPAQueryFactory jpaQueryFactory;

    @Override
    public long update(Long id, Integer age) {
        QUser user = QUser.user;
        return jpaQueryFactory.update(user)
                .set(user.age, age)     // 更新字段的值
                .where(user.id.eq(id))  // 筛选条件
                .execute();
    }

    @Override
    public Long delete(Long id) {
        QUser user = QUser.user;
        return jpaQueryFactory.delete(user)
                .where(user.id.eq(id))  // 筛选条件
                .execute();
    }

    /**
     * DOC: queryDSL 1.常规查询
     *
     * @param userId
     * @return
     */
    @Override
    public User queryById(Long userId) {
        QUser user = QUser.user;
        return jpaQueryFactory.selectFrom(user)
                .where(user.id.eq(userId)
                                .and(user.deleted.eq(0))
//                        .and(user.username.contains("xxx"))
//                        .and(user.username.like("%" + "xxx" + "%"))
//                        .and(user.creationDate.loe(LocalDateTime.now()))
                ).fetchOne();
    }

    /**
     * DOC: queryDSL 2.动态条件查询
     *
     * @param queryUser
     * @return
     */
    @Override
    public List<User> queryByBooleanBuilder(User queryUser) {
        QUser user = QUser.user;
        BooleanBuilder builder = new BooleanBuilder();
        if (null != queryUser.getId())
            builder.and(user.id.eq(queryUser.getId()));
        if (!StringUtils.isEmpty(queryUser.getUsername()))
            builder.and(user.username.like("%" + queryUser.getUsername() + "%"));
        if (null != queryUser.getCreationDate())
            builder.and(user.creationDate.between(queryUser.getCreationDate(), queryUser.getCreationDate().plusDays(1)));

        return jpaQueryFactory.selectFrom(user).where(builder).fetch();
    }

    /**
     * DOC: queryDSL 3.子查询
     * 此处的查询只是为了演示用法，无可用价值
     *
     * @param managerId
     * @return
     */
    @Override
    public List<User> queryWithSubQuery(Long managerId) {
        QUser user = QUser.user;
        return jpaQueryFactory.selectFrom(user)
                .where(user.id.in(
                        JPAExpressions.select(user.id)
                                .from(user)
                                .where(user.managerId.eq(managerId))
                )).fetch();
    }

    /**
     * DOC: queryDSL 4.联表查询并将结果封装到DTO中(测试不能同表自关联查询，应该要替换成子查询？)
     *
     * @param managerId 领导id
     * @return 领导的所有员工信息
     */
    @Override
    public List<UserDTO> queryWithJoin(Long managerId) {
        QUser2018 managerUser = QUser2018.user2018;
        QUser user = QUser.user;

        return jpaQueryFactory.select(
                        Projections.bean(UserDTO.class, user.username, managerUser.username.as("managerName"))
                ).from(managerUser)
                .leftJoin(user).on(managerUser.id.eq(user.managerId))
                .where(managerUser.id.eq(managerId))
                .fetch();
    }

    @Override
    public void queryOther() {
        QUser user = QUser.user;
        List<String> distinctNameList = jpaQueryFactory.selectDistinct(user.username).from(user).fetch();
        log.info("a. 去重查询：{}", distinctNameList);

        User first = jpaQueryFactory.selectFrom(user).fetchFirst();
        User user1 = jpaQueryFactory.select(user).from(user).fetchFirst();
        JPAQuery<User> userJPAQuery = jpaQueryFactory.selectFrom(user).orderBy(user.id.desc()).fetchAll();

        log.info("b. fetchFirst查询首个结果：{}, {}", first, user1);

        // 查询单个实体，如果结果有多个，会抛`NonUniqueResultException`。
        try {
            User one = jpaQueryFactory.selectFrom(user).fetchOne();
            log.info("fetchOne查询首个结果：{}", first);
        } catch (NonUniqueResultException e) {
            log.error(e.getMessage());
        }

        // DOC: queryDSL 5.聚合函数的使用
        // avg() & sum()
        Double avgAge = jpaQueryFactory.select(user.age.avg()).from(user).fetchOne();
        log.info("聚合函数 avg() 查询平均年龄结果：{}", avgAge);
        // concat(val) 返回结果拼接
        String nameConcat = jpaQueryFactory.select(user.username.concat("_2018")).from(user).fetchFirst();
        log.info("聚合函数 concat(val) 拼接返回结果：{}", nameConcat);

        // contains(val) 判断是否包含结果
        Boolean containsFlag = jpaQueryFactory.select(user.username.contains("王")).from(user).where(user.id.eq(2L)).fetchOne();
        log.info("聚合函数 contains(val) 判断是否包含结果：{}", containsFlag);

        String dateStr = jpaQueryFactory.select(
                Expressions.stringTemplate("DATE_FORMAT({0}, '%Y-%m-%d')", user.creationDate)
        ).from(user).fetchFirst();
        log.info("聚合函数 DATE_FORMAT() 结果：{}", dateStr);
    }


}
